package cn.mydsl.restful

enum class Keyword(val VALUE: String) {
    SELECT("SELECT"), INSERT("INSERT")
    ,
    UPDATE("UPDATE"), DELETE("DELETE")
}
//认证的元数据，描述 某用户,对某表,进行某操作时,需要满足的条件
open class AuthMtadata(val tableName: String, val keyword: Keyword, var ableFields: List<Field>, var range: String?=null){
    override
    fun toString(): String {
        return "${this.javaClass.simpleName}(tableName=${tableName},keyword=${keyword},ableFields=${ableFields},range=${range})"
    }

    /**
     * SELCT到WHERE之后的表关联条件（JointRelate.LINKED除外）
     * 手动直接指定字段，相当于直接指定表，不考虑joint
     */
    fun sqlSelectFromWhere(needTableMap:Map<String,JointRelate>,includeFields:List<FilterField>):String{
        var sqlSelect =""
        var sqlFrom =""
        var sqlwhere =""
        if(this.keyword!=Keyword.SELECT ||this.ableFields.size==0 || includeFields.size==0){
            return ""
        }
        for (ableField in this.ableFields) {
            if(includeFields.firstOrNull({f->f.name==ableField.name})==null){
                //未包含该字段
                continue
            }
            //遇到时间类型，转换成秒
            if(ableField.type==FieldType.DATA){
                sqlSelect += "UNIX_TIMESTAMP(${ableField.name}) AS ${ableField.name.substringAfterLast(".")} ,"
            }else{
                sqlSelect += "${ableField.name},"
            }
        }
        if(sqlSelect.isNotEmpty()){
            sqlFrom+="${this.tableName},"
        }
        var needTables=needTableMap //赋值后，便于修改
        //其他表的字段
        for(includeField in includeFields){
            if(includeField.tableName!=this.tableName){
                //要考虑字段重复，导致只显示一个,对关联表字段，重命名 规则是，去掉.并把之后的字段大写
                if(sqlSelect.contains(includeField.name.substringAfterLast("."))){
                    if(sqlSelect.contains(includeField.name.substringAfter("."))){
                        var rename=includeField.name.replace("`","")
                        var i=rename.indexOfFirst { c->c.equals('.') }
                        while (i!=-1){
                            rename=rename.replace(".${rename[i+1]}","${rename[i+1].toUpperCase()}")
                            i=rename.indexOfFirst { c->c.equals('.') }
                        }
                        //遇到时间类型，转换成秒
                        if(includeField.type==FieldType.DATA){
                            sqlSelect += "UNIX_TIMESTAMP(${includeField.name}) AS `$rename`,"
                        }else{
                            sqlSelect += "${includeField.name} AS `$rename`,"
                        }
                    }else{
                        var rename=includeField.name.substringAfter(".").replace("`","")
                        var i=rename.indexOfFirst { c->c.equals('.') }
                        while (i!=-1){
                            rename=rename.replace(".${rename[i+1]}","${rename[i+1].toUpperCase()}")
                            i=rename.indexOfFirst { c->c.equals('.') }
                        }
                        //遇到时间类型，转换成秒
                        if(includeField.type==FieldType.DATA){
                            sqlSelect += "UNIX_TIMESTAMP(${includeField.name}) AS `$rename`,"
                        }else{
                            sqlSelect += "${includeField.name} AS `$rename`,"
                        }
                    }
                }else{
                    //遇到时间类型，转换成秒
                    if(includeField.type==FieldType.DATA){
                        sqlSelect += "UNIX_TIMESTAMP(${includeField.name}) AS ${includeField.name.substringAfterLast(".")},"
                    }else{
                        sqlSelect += "${includeField.name},"
                    }
                }

                if(!sqlFrom.contains(includeField.tableName)){
                    sqlFrom+="${includeField.tableName},"
                    //删除已经加入的表
                    needTables=needTables.filter {entry-> !entry.key.equals(includeField.tableName)  }
                    //没有关联关系的表，即使指定joint 也不会引入
                    if(includeField.jointRelate==JointRelate.MANY){
                        //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                        sqlwhere+="${this.tableName}.`id`=${includeField.tableName}.`${this.tableName.replace("`","").substringAfterLast(".")}Id` AND "
                    } else if(includeField.jointRelate==JointRelate.BELONG){
                        //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                        sqlwhere+="${this.tableName}.`${includeField.tableName.replace("`","").substringAfterLast(".")}Id`=${includeField.tableName}.`id` AND "
                    }else{
                        //被joint的表且无关系，但在needTables列表中，属于已经 JointRelate.LINKED 的表，则只加字段，关联操作会在处理 添加查询条件处，处理
                    }
                }
            }
        }
        for(needTable in needTables){
            if(needTable.value==JointRelate.MANY) {
                sqlFrom+="${needTable.key},"
                //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                sqlwhere+="${this.tableName}.`id`=${needTable.key}.`${this.tableName.replace("`","").substringAfterLast(".")}Id` AND "
            } else if(needTable.value==JointRelate.BELONG){
                sqlFrom+="${needTable.key},"
                //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                sqlwhere+="${this.tableName}.`${needTable.key.replace("`","").substringAfterLast(".")}Id`=${needTable.key}.`id` AND "
            }else if(needTable.value==JointRelate.LINKED){
                //未被joint，则无需输出字段，只加表，关联操作会在处理 添加查询条件处，处理
                sqlFrom+="${needTable.key},"
            }
        }
        sqlSelect=sqlSelect.substringBeforeLast(",")
        sqlFrom=sqlFrom.substringBeforeLast(",")
        sqlwhere=sqlwhere.substringBeforeLast("AND")
        return "SELECT $sqlSelect FROM ${sqlFrom} WHERE $sqlwhere"
    }

    /**
     * SELCT到WHERE之后的表关联条件（JointRelate.LINKED除外）
     */
    fun sqlSelectFromWhere(joinAuthMtadatalist:List<JointAuthMtadata>,needTableMap:Map<String,JointRelate>,excludeFields:List<FilterField>):String{
        var sqlSelect =""
        var sqlFrom =""
        var sqlwhere =""
        if(this.keyword!=Keyword.SELECT ||this.ableFields.size==0){
            return ""
        }
        for (ableField in this.ableFields) {
            if(excludeFields.firstOrNull({f->f.name==ableField.name})!=null){
                //需要排除的字段
               continue
            }
            //遇到时间类型，转换成秒
            if(ableField.type==FieldType.DATA){
                sqlSelect += "UNIX_TIMESTAMP(${ableField.name}) AS ${ableField.name.substringAfterLast(".")} ,"
            }else{
                sqlSelect += "${ableField.name},"
            }
        }
        //if(sqlSelect.isNotEmpty()){
            sqlFrom+="${this.tableName},"
        //}
        var needTables=needTableMap //赋值后，便于修改
        for(joinAuthMtadata  in joinAuthMtadatalist){
            if(joinAuthMtadata.keyword!=Keyword.SELECT || joinAuthMtadata.ableFields.size==0 || this.ableFields.size==0 || joinAuthMtadata.tableName==this.tableName
                    || (joinAuthMtadata.jointRelate!=JointRelate.MANY
                    && joinAuthMtadata.jointRelate!=JointRelate.BELONG
                    && needTables[joinAuthMtadata.tableName]!=JointRelate.LINKED )){
                //无可用字段，无关系则 或 被joint表和当前表相同则跳过
                continue
            }

            for (ableField in joinAuthMtadata.ableFields) {
                if(excludeFields.firstOrNull({f->f.name==ableField.name})!=null){
                    //需要排除的字段
                    continue
                }
                //要考虑字段重复，导致只显示一个,对关联表字段，重命名 规则是，去掉.并把之后的字段大写
                if(sqlSelect.contains(ableField.name.substringAfterLast("."))){
                    if(sqlSelect.contains(ableField.name.substringAfter("."))){
                        var rename=ableField.name.replace("`","")
                        var i=rename.indexOfFirst { c->c.equals('.') }
                        while (i!=-1){
                            rename=rename.replace(".${rename[i+1]}","${rename[i+1].toUpperCase()}")
                            i=rename.indexOfFirst { c->c.equals('.') }
                        }
                        //遇到时间类型，转换成秒
                        if(ableField.type==FieldType.DATA){
                            sqlSelect += "UNIX_TIMESTAMP(${ableField.name}) AS `$rename`,"
                        }else{
                            sqlSelect += "${ableField.name} AS `$rename`,"
                        }
                    }else{
                        var rename=ableField.name.substringAfter(".").replace("`","")
                        var i=rename.indexOfFirst { c->c.equals('.') }
                        while (i!=-1){
                            rename=rename.replace(".${rename[i+1]}","${rename[i+1].toUpperCase()}")
                            i=rename.indexOfFirst { c->c.equals('.') }
                        }
                        //遇到时间类型，转换成秒
                        if(ableField.type==FieldType.DATA){
                            sqlSelect += "UNIX_TIMESTAMP(${ableField.name}) AS `$rename`,"
                        }else{
                            sqlSelect += "${ableField.name} AS `$rename`,"
                        }
                    }
                }else{
                    //遇到时间类型，转换成秒
                    if(ableField.type==FieldType.DATA){
                        sqlSelect += "UNIX_TIMESTAMP(${ableField.name}) AS ${ableField.name.substringAfterLast(".")},"
                    }else{
                        sqlSelect += "${ableField.name},"
                    }
                }
            }
            sqlFrom+="${joinAuthMtadata.tableName},"
            //删除已经加入的表
            needTables=needTables.filter {entry-> !entry.key.equals(joinAuthMtadata.tableName)  }
            //没有关联关系的表，即使指定joint 也不会引入
            if(joinAuthMtadata.jointRelate==JointRelate.MANY){
                //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                sqlwhere+="${this.tableName}.`id`=${joinAuthMtadata.tableName}.`${this.tableName.replace("`","").substringAfterLast(".")}Id` AND "
            } else if(joinAuthMtadata.jointRelate==JointRelate.BELONG){
                //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                sqlwhere+="${this.tableName}.`${joinAuthMtadata.tableName.replace("`","").substringAfterLast(".")}Id`=${joinAuthMtadata.tableName}.`id` AND "
            }else{
                //被joint的表且无关系，但在needTables列表中，属于已经 JointRelate.LINKED 的表，则只加字段，关联操作会在处理 添加查询条件处，处理
            }
        }
        for(needTable in needTables){
            if(needTable.value==JointRelate.MANY) {
                sqlFrom+="${needTable.key},"
                //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                sqlwhere+="${this.tableName}.`id`=${needTable.key}.`${this.tableName.replace("`","").substringAfterLast(".")}Id` AND "
            } else if(needTable.value==JointRelate.BELONG){
                sqlFrom+="${needTable.key}},"
                //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                sqlwhere+="${this.tableName}.`${needTable.key.replace("`","").substringAfterLast(".")}Id`=${needTable.key}.`id` AND "
            }else if(needTable.value==JointRelate.LINKED){
                //未被joint，则无需输出字段，只加表，关联操作会在处理 添加查询条件处，处理
                sqlFrom+="${needTable.key}},"
            }
        }
        sqlSelect=sqlSelect.substringBeforeLast(",")
        sqlFrom=sqlFrom.substringBeforeLast(",")
        sqlwhere=sqlwhere.substringBeforeLast("AND")
        return "SELECT $sqlSelect FROM ${sqlFrom} WHERE $sqlwhere"
    }

//    /**
//     * 权限要求锁定的范围
//     */
//    已经在上层处理
//    fun sqlWhereRange( ):String{
//        var whereRange = this.range
//        if (whereRange != null && whereRange.isNotEmpty()) {
//            var matchResults = "\\[([\\w]+)\\]".toRegex().findAll(whereRange.trim())
//            for (groups in matchResults) {
//                var guserValue = groups.groups[1]
//                if (guserValue != null) {
//                    when (guserValue.value.toLowerCase()) {
//                        "uid" -> whereRange = whereRange?.replace("[uid]", "'${CommonResourceServer.user.uid.replace("'", "''")}'")
//                        "oid" -> whereRange = whereRange?.replace("[oid]", "'${CommonResourceServer.user.oid.replace("'", "''")}'")
//                        "jid" -> whereRange = whereRange?.replace("[jid]", "'${CommonResourceServer.user.jid.replace("'", "''")}'")
//                        else -> throw Exception("还有未格式化的操作范围：${this.range}")
//                    }
//                }
//            }
//            //追加where范围
//            if (whereRange != null && whereRange.isNotEmpty()) {
//                whereRange = "($whereRange)"
//                return whereRange
//            }
//        }
//        return ""
//    }

}

class JointAuthMtadata(tableName: String,keyword: Keyword,ableFields: List<Field>,range: String?=null,val jointRelate:JointRelate=JointRelate.NONE):AuthMtadata(tableName,keyword,ableFields,range){
    override
    fun toString(): String {
        return "${this.javaClass.simpleName}(tableName=${tableName},keyword=${keyword},ableFields=${ableFields},range=${range},jointRelate=${jointRelate.relate})"
    }
}

//条件操作
enum class Operat(val urlStr: String, val sqlOperat: String) {
    LIKE("lk", "LIKE"), RANGE("rng", "")
    ,
    EQUAL("eq", "="), NOT_EQUAL("!eq", "<>")
    ,
    IN("in", "IN"), NOT_IN("!in", "NOT IN")
    ,
    LT("lt", "<"), LT_EQUAL("lteq", "<=")
    ,
    GT("gt", ">"), GT_EQUAL("gteq", ">=")
    ,
    ASC("asc", "ASC"), DESC("desc", "DESC")
    ,
    INCL("incl",""),EXCL("excl","")
    ,
    FORMAT("fmt", "")
}

enum class FieldType(val splitStr: String) {
    NUMBER(""), STRING("'"), DATA("'"), FORMAT("fmt")
}

/**
 * 关联关系
 */
enum class JointRelate(val relate: String){
    //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
    //本表belong_to关联表（特点是本表有关联表_id字段） join表是主表
    NONEED("currentTable and jointTable is same table"),
    MANY("currentTable has many jointTable"), BELONG("currentTable belong to jointTable"),
    LINKED("currentTable manual joint jointTable"),NONE("currentTable not joint jointTable")
}
/**
 * 存储,数据库字段信息 name 必须是db.table.field形式
 */
open class Field(val name: String, val type: FieldType = FieldType.STRING) {
    val tableName: String
        get(){
            return this.name.substringBeforeLast(".")
        }
    override
    fun toString(): String {
        return "${this.javaClass.simpleName}(name=${name},type=${type})"
    }
}

class FilterField(name: String, type: FieldType = FieldType.STRING,var jointRelate:JointRelate=JointRelate.NONE,var value: List<String>? = null):Field(name,type){
    val filterValue: List<String>? = null
        get(){
            //从常量value转为filterValue
            var values= this.value
            if (field==null && values != null) {
                var vs = mutableListOf<String>()
                for (v in values) {
                    if(this.type== FieldType.STRING || this.type== FieldType.DATA){
                        if ((v.trim().startsWith("'") && v.trim().endsWith("'")) || (v.trim().startsWith("\"") && v.trim().endsWith("\""))) {
                            //自带单引号和双引号包裹,则去掉
                            vs.add(v.trim().substring(1, v.length).replace("'", "''"))
                        } else {
                            vs.add(v.trim().replace("'", "''")) //不能去掉空格
                        }
                    }else if(this.type== FieldType.NUMBER){
                        //因为该类型,是直接填充，没有单引号和双引号包裹，为防止注入，必须是数字
                        try {
                            if(v.trim().contains('.')){
                                var i=v.trim().toDouble()
                                vs.add(i.toString())
                            }else{
                                var i=v.trim().toLong()
                                vs.add(i.toString())
                            }
                        }catch(e:Exception){
                            vs.add("") //以空 代替,满足如s[rng[fn]]=,22 的需求
                            //println("${v}to Int:${e.printStackTrace()}")
                        }
                    }else if(this.type== FieldType.FORMAT){
                        vs.add(v)
                        break //只取第一个
                    }
                }
                field=vs.toList()//从常量value转为filterValue
            }
            return field;
        }

    override
    fun toString(): String {
        return "${this.javaClass.simpleName}(name=${name},type=${type},jointRelate=${jointRelate.relate},value=${value})"
    }

    /**
     * name,因为格式未db.table.field,所以作为相等的
     */
    override fun equals(other: Any?): Boolean {
        if (other == null) return false
        if (other is FilterField) {
            if (other.name == this.name && other.value == this.value) return true
        }
        return false
    }


    /**
     * 重写hashcode 方法，返回的hashCode 不一样才认定为不同的对象
     */
    override fun hashCode(): Int {
        var v=value
        if(v!=null){
            return name.hashCode()*v.hashCode()
        }
        return name.hashCode()
    }


    //所有筛选值以单引号‘包裹，对于筛选值数据里的单引号，替换成2个单引号，其他的不变，即可防止注入
    fun sql(operat: Operat): String {
        var vs = this.filterValue
        var sqlValue: String = ""
        if (vs != null && vs.size > 0) {
            when (operat) {
            //只取第一个,字符串为空,则不填充关键字
                Operat.NOT_EQUAL, Operat.EQUAL, Operat.LIKE ->if(vs[0].isNotEmpty())  sqlValue += "${this.name} ${operat.sqlOperat} ${this.type.splitStr}${vs[0]}${this.type.splitStr} " else sqlValue+=""
            //只取第2个
                Operat.RANGE -> {
                    if (vs.size >= 2) {
                        if (vs[0].isNotEmpty()) {
                            sqlValue += "${this.name} >= ${this.type.splitStr}${vs[0]}${this.type.splitStr}"
                            if (vs[1].isNotEmpty() ) {
                                sqlValue += " AND ${this.name} <= ${this.type.splitStr}${vs[1]}${this.type.splitStr}"
                                sqlValue = "($sqlValue)"
                            }
                        } else if (vs[1].isNotEmpty()) {
                            sqlValue += "${this.name} <= ${this.type.splitStr}${vs[1]}${this.type.splitStr}"
                        }
                    }
                }
                Operat.IN, Operat.NOT_IN -> {
                    for (v in vs) {
                        //字符串为空,则不填充关键字
                        if(v.isNotEmpty()){
                            sqlValue += "${this.type.splitStr}${v}${this.type.splitStr},"
                        }
                    }
                    //,字符串为空,则不填充关键字
                    if(sqlValue.isNotEmpty()){
                        sqlValue = "${this.name} ${operat.sqlOperat} (${sqlValue.substringBeforeLast(",")})"
                    }
                }
                else -> sqlValue = ""
            }
        }
        return sqlValue
    }
}

//defaultDbNameStr可能带有`和空格,nameStr正则时已经过滤`
fun Map<String, Field>.find(defaultDbNameStr: String,currentTable:String, nameStr: String):FilterField?{
    var field:Field?=null
    //1.只给字段名,则其他信息和当前表的相同
    val key1: String = "${currentTable}.`${nameStr}`"
    if (this.contains(key1)) {
        field=this[key1]
    }
    //2.给了字段名和表名，则加上默认库名即可
    val key2= "`${defaultDbNameStr.trim().replace("`","")}`.`${nameStr.replace(".","`.`")}`"
    if (this.contains(key2)) {
        field=this[key2]
    }
    //3.给了库明，表名和字段名
    val key3= "`${nameStr.replace(".","`.`")}`"
    if (this.contains(key3)) {
        field=this[key3]
    }
    var filterField:FilterField?=null
    if(field!=null){

        val joinTableName=field.tableName
        if(joinTableName==currentTable){
            filterField=FilterField(field.name,field.type,JointRelate.NONEED)
        }else{
            //判断 相对于当前表，join表是主表还是子表
            //没有外键关系，则需要从参数获取cmp[field1|eq|field2]手工指定关系
            if(this.containsKey("${joinTableName}.`${currentTable.replace("`","").substringAfterLast(".")}Id`")){
                //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                filterField=FilterField(field.name,field.type,JointRelate.MANY)
            } else if(this.containsKey("${currentTable}.`${joinTableName.replace("`","").substringAfterLast(".")}Id`")){
                //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                filterField=FilterField(field.name,field.type,JointRelate.BELONG)
            }else{
                filterField=FilterField(field.name,field.type,JointRelate.NONE)
            }
        }
    }

    return filterField
}

//defaultDbNameStr和tableStr可能带有`和空格
fun Map<String, AuthMtadata>.find(defaultDbNameStr: String, tableStr: String, keyword: Keyword):AuthMtadata?{
    //1.只给字表名,则加上默认库名即可
    var key: String = "`${defaultDbNameStr.trim().replace("`","")}`.`${tableStr.trim().replace("`","")}`"+keyword.VALUE
    if (this.contains(key)) {
        return this[key]
    }
    //2.给了库明，表名
    key= "`${tableStr.trim().replace("`","").replace(".","`.`")}`"+keyword.VALUE
    if (this.contains(key)) {
        return this[key]
    }
    return null
}

fun Map<String, AuthMtadata>.find(defaultDbNameStr: String, tableStrs: List<String>, keyword: Keyword,currentTable:String,fieldMap:Map<String, Field>):List<JointAuthMtadata>{
    var authMtadataList= mutableListOf<JointAuthMtadata>()
    for(tableStr in tableStrs){
       var authMtadata=this.find(defaultDbNameStr,tableStr,keyword)
        if(authMtadata!=null){
            //填充 关联关系字段
            //判断 相对于当前表，join表是主表还是子表
            //没有外键关系，则需要从参数获取cmp[field1|eq|field2]手工指定关系
            if(fieldMap.containsKey("${authMtadata.tableName}.`${currentTable.replace("`","").substringAfterLast(".")}Id`")){
                //本表has_many 关联表 （特点是关联表有本表_id字段） join表是子表
                authMtadataList.add(JointAuthMtadata(authMtadata.tableName,authMtadata.keyword,authMtadata.ableFields,authMtadata.range,JointRelate.MANY))
            } else if(fieldMap.containsKey("${currentTable}.`${authMtadata.tableName.replace("`","").substringAfterLast(".")}Id`")){
                //本表belongs_to关联表（特点是本表有关联表_id字段） join表是主表
                authMtadataList.add(JointAuthMtadata(authMtadata.tableName,authMtadata.keyword,authMtadata.ableFields,authMtadata.range,JointRelate.BELONG))
            }
        }
    }
    return authMtadataList
}

